package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model.Certificate;
import model.Course;
import model.Emp;
import model.Train;
import util.DBUtil;
import util.Page;
import dao.CertificateDAO;

public class CertificateDAOImpl implements CertificateDAO {

	public Page<Certificate> getAllCertificate(int pageNo, int pageSize) {
		Page<Certificate> page = new Page<Certificate>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Certificate> list = new ArrayList<Certificate>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from CERTIFICATE";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from CERTIFICATE) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Certificate  c = new Certificate();
				c.setId(rs.getInt("id"));
				Emp e=new Emp();
				e=new EmpDAOImpl().getEmpById(rs.getInt("EMPID"));
				c.setEmp(e);
				Train t=new Train();
				t=new TrainDAOImpl().getById(rs.getInt("TRAINID"));
				c.setTrain(t);
				c.setCername(rs.getString("CERNAME"));
				c.setCerid(rs.getString("CERID"));
				c.setCerdate(rs.getDate("CERDATE"));
				c.setDeadline(rs.getDate("DEADLINE"));
				c.setDutyflag(rs.getInt("DUTYFLAG")==0?false:true);
				c.setSalflag(rs.getInt("SALFLAG")==0?false:true);
				list.add(c);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Certificate> getCertificateByEmp(int pageNo, int pageSize,
			Emp emp) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<Certificate> getCertificateByCerid(int pageNo, int pageSize,
			String cerid) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<Certificate> getCertificateByCerid(int pageNo, int pageSize,
			Date starttime, Date endtime) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<Certificate> getCertificateByTrain(int pageNo, int pageSize,
			Train train) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean addCertificate(Certificate cf) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn=dbu.getConnection();
			String sql="insert into CERTIFICATE (ID,EMPID,TRAINID,CERNAME,CERID,CERDATE,DEADLINE,DUTYFLAG,SALFLAG) values(CERTIFICATE_ID.nextval,?,?,?,?,?,?,?,?)";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, cf.getEmp().getId());
			pstat.setInt(2, cf.getTrain().getId());
			pstat.setString(3, cf.getCername());
			pstat.setString(4, cf.getCerid());
			pstat.setDate(5, new java.sql.Date(cf.getCerdate().getTime()));
			pstat.setDate(6, new java.sql.Date(cf.getDeadline().getTime()));
			pstat.setInt(7, 0);
			pstat.setInt(8, 0);
			pstat.executeQuery();
			flag=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public boolean updateCertificate(Certificate cf) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn=dbu.getConnection();
			String sql="update CERTIFICATE set CERNAME=?,CERID=?,CERDATE=?,DEADLINE=? where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setString(1, cf.getCername());
			pstat.setString(2, cf.getCerid());
			pstat.setDate(3, new java.sql.Date(cf.getCerdate().getTime()));
			pstat.setDate(4, new java.sql.Date(cf.getDeadline().getTime()));
			pstat.setInt(5, cf.getId());
			pstat.executeUpdate();
			flag=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public boolean deleteCertificate(Certificate certificate) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean deleteById(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn=dbu.getConnection();
			String sql="delete from CERTIFICATE  where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, id);
			pstat.executeUpdate();
			flag=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public Certificate getById(int id) {		
		
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		Certificate  c=null;
		try {
			conn = dbu.getConnection();
			String sql = "select * from CERTIFICATE where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while (rs.next()) {
				c = new Certificate();
				c.setId(rs.getInt("id"));
				Emp e=new Emp();
				e=new EmpDAOImpl().getEmpById(rs.getInt("EMPID"));
				c.setEmp(e);
				Train t=new Train();
				t=new TrainDAOImpl().getById(rs.getInt("TRAINID"));
				c.setTrain(t);
				c.setCername(rs.getString("CERNAME"));
				c.setCerid(rs.getString("CERID"));
				c.setCerdate(rs.getDate("CERDATE"));
				c.setDeadline(rs.getDate("DEADLINE"));
				c.setDutyflag(rs.getInt("DUTYFLAG")==0?false:true);
				c.setSalflag(rs.getInt("SALFLAG")==0?false:true);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return c;
	}

	public Page<Certificate> getAllCertificateByEmpName(int pageNo,
			int pageSize, String name) {
		Page<Certificate> page = new Page<Certificate>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Certificate> list = new ArrayList<Certificate>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from CERTIFICATE c,emp e where e.id=c.empid and  e.name like'%"+name+"%'";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select c.* from CERTIFICATE c,emp e where e.id=c.empid and  e.name like'%"+name+"%') s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Certificate  c = new Certificate();
				c.setId(rs.getInt("id"));
				Emp e=new Emp();
				e=new EmpDAOImpl().getEmpById(rs.getInt("EMPID"));
				c.setEmp(e);
				Train t=new Train();
				t=new TrainDAOImpl().getById(rs.getInt("TRAINID"));
				c.setTrain(t);
				c.setCername(rs.getString("CERNAME"));
				c.setCerid(rs.getString("CERID"));
				c.setCerdate(rs.getDate("CERDATE"));
				c.setDeadline(rs.getDate("DEADLINE"));
				c.setDutyflag(rs.getInt("DUTYFLAG")==0?false:true);
				c.setSalflag(rs.getInt("SALFLAG")==0?false:true);
				list.add(c);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Certificate> getAllCertificateByCerName(int pageNo,
			int pageSize, String cername) {
		Page<Certificate> page = new Page<Certificate>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Certificate> list = new ArrayList<Certificate>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from CERTIFICATE where CERNAME like'%"+cername+"%'";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from CERTIFICATE  where CERNAME like'%"+cername+"%') s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Certificate  c = new Certificate();
				c.setId(rs.getInt("id"));
				Emp e=new Emp();
				e=new EmpDAOImpl().getEmpById(rs.getInt("EMPID"));
				c.setEmp(e);
				Train t=new Train();
				t=new TrainDAOImpl().getById(rs.getInt("TRAINID"));
				c.setTrain(t);
				c.setCername(rs.getString("CERNAME"));
				c.setCerid(rs.getString("CERID"));
				c.setCerdate(rs.getDate("CERDATE"));
				c.setDeadline(rs.getDate("DEADLINE"));
				c.setDutyflag(rs.getInt("DUTYFLAG")==0?false:true);
				c.setSalflag(rs.getInt("SALFLAG")==0?false:true);
				list.add(c);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Certificate> getCertificateByTime(int pageNo, int pageSize,
			Date starttime, Date endtime) {
		Page<Certificate> page = new Page<Certificate>(pageNo, pageSize);
		int totalCount = 0;
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		List<Certificate> list = new ArrayList<Certificate>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from CERTIFICATE where CERDATE between ? and ?";
			pstat=conn.prepareStatement(sql);
			pstat.setDate(1, new java.sql.Date(starttime.getTime()));
			pstat.setDate(2, new java.sql.Date(endtime.getTime()) );
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select * from CERTIFICATE where CERDATE between ? and ?) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setDate(1, new java.sql.Date(starttime.getTime()));
			pstat.setDate(2, new java.sql.Date(endtime.getTime()) );
			pstat.setInt(3, page.getPageNo()*pageSize);
			pstat.setInt(4,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Certificate  c = new Certificate();
				c.setId(rs.getInt("id"));
				Emp e=new Emp();
				e=new EmpDAOImpl().getEmpById(rs.getInt("EMPID"));
				c.setEmp(e);
				Train t=new Train();
				t=new TrainDAOImpl().getById(rs.getInt("TRAINID"));
				c.setTrain(t);
				c.setCername(rs.getString("CERNAME"));
				c.setCerid(rs.getString("CERID"));
				c.setCerdate(rs.getDate("CERDATE"));
				c.setDeadline(rs.getDate("DEADLINE"));
				c.setDutyflag(rs.getInt("DUTYFLAG")==0?false:true);
				c.setSalflag(rs.getInt("SALFLAG")==0?false:true);
				list.add(c);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public boolean changeCertificateFlag(int id) {
		DBUtil dbu = new DBUtil();
		Connection conn = null;
		PreparedStatement pstat = null;
		ResultSet rs = null;
		boolean flag=false;
		try {
			conn=dbu.getConnection();
			String sql="update  CERTIFICATE set SALFLAG=1 where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, id);
			pstat.executeUpdate();
			flag=true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

}
